package ch.hslu.hs12.dmg;


import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateBasicData {


	private int selector;
	private BufferedReader bin = new BufferedReader(new InputStreamReader(
			System.in));

	
	// Dialog to add a Mitarbeiter to the database
	public void addMitarbeiter(Connection conn) {
		
		String name;
		int salary;
		String address;
		String bank;
		String aDate;

		try {
			System.out.println("Please enter the name: ");
			name = bin.readLine();

			System.out.println("Please enter the address: ");
			address = bin.readLine();

			System.out.println("Please enter the bank: ");
			bank = bin.readLine();

			System.out.println("Please enter the birth date (JJJJ-MM-DD): ");
			aDate = bin.readLine();

			System.out.println("Please enter the salary: ");
			String aSalary = bin.readLine();
			salary = Integer.parseInt(aSalary);


		Statement myQuery;
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Mitarbeiter (Name, Gehalt, Adresse, Bankverbindung, Geburtstag)"
					+ " VALUES('"+ name + "', " + salary + ", '" + address+ "','" + bank + "', '"+ aDate +"')");

			conn.commit();

		} catch (SQLException e) {
			System.out.println("SQLException while trying to add a Mitarbeiter");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("IOException while trying to add a Mitarbeiter");
			e.printStackTrace();
		}

	}
	
	//Dialog to add a machine to the database
	public void addMaschine(Connection conn){
		try {
		
			
			String machineName = null;
		
		do{
		System.out.println("Which type of machine would you like to add? \n" +
				"1: Vorderradmaschine \n" +
				"2: Hinterradmaschine \n" +
				"3: Velomaschine \n");
		
				selector = Integer.parseInt(bin.readLine());

			if(!((selector == 1 | selector == 2 | selector == 3))){
				System.out.println("This machine is not available, please enter again.");
			}

		}while(!(selector == 1 | selector == 2 | selector == 3));
		
		switch(selector){
		case 1: machineName = "Vorderradmaschine";
		break;
		case 2: machineName = "Hinterradmaschine";
		break;
		case 3: machineName = "Velomaschine";
		break;
		default: System.out.println("Invalid machine");
		}
		
		
		Statement myQuery;
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Maschine (Bezeichnung, Wartungsinterval)" +
					" VALUES ('"+ machineName +"', 5)");			
			
			conn.commit();
			
			System.out.println("Added "+ machineName);
			
		} catch (NumberFormatException | IOException e) {
			System.out.println("IO Exception while creating a new machine");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("SQLException while creating a new machine");
			e.printStackTrace();
		}

	}

	//Dialog to add a Basisteil to the database
	public void addBasisteil(Connection conn) {
try {
		
			
			String basisTeilName = null;
			int price = 0;
			String size = null;
		
		do{
		System.out.println("Which type of Basisteil would you like to add? \n" +
				"1: Nabe \n" +
				"2: Speiche \n" +
				"3: Felge \n");
		
				selector = Integer.parseInt(bin.readLine());

			if(!((selector == 1 | selector == 2 | selector == 3))){
				System.out.println("This Basisteil is not available, please enter again.");
			}

		}while(!(selector == 1 | selector == 2 | selector == 3));
		
		switch(selector){
		case 1: basisTeilName = "Nabe";
		break;
		case 2: basisTeilName = "Speiche";
		break;
		case 3: basisTeilName = "Felge";
		break;
		default: System.out.println("Invalid Basisteil");
		}
		
		do{
		System.out.println("What is the size of the new "+basisTeilName+" \n" +
				"1: 26 Zoll \n" +
				"2: 27.5 Zoll \n" +
				"3: 29 Zoll \n");
		
				selector = Integer.parseInt(bin.readLine());

			if(!((selector == 1 | selector == 2 | selector == 3))){
				System.out.println("This Basisteil is not available, please enter again.");
			}

		}while(!(selector == 1 | selector == 2 | selector == 3));
		
		switch(selector){
		case 1: size = "26 Zoll";
		break;
		case 2: size = "27.5 Zoll";
		break;
		case 3: size = "29 Zoll";
		break;
		default: System.out.println("Invalid size");
		}
		
		System.out.println("Please enter the price: ");
		price = Integer.parseInt(bin.readLine());
		
		Statement myQuery;
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Teile (Groessenangabe, Preis, Bezeichnung, Typ)" +
					" VALUES ('"+size+"', "+price+", '"+basisTeilName+"', 0)");			
			
			
			conn.commit();
			
				int lieferantenID = this.addLieferant(conn);
				
				Statement stmt = conn.createStatement ();
				ResultSet rs = stmt.executeQuery("SELECT *" +
						" FROM Teile" +
						" WHERE Teilnr=(SELECT MAX(Teilnr) FROM Teile)");		
				rs.next();
				int teilNr = rs.getInt("Teilnr");
				rs.close();
				
				myQuery = conn.createStatement();

				myQuery.executeUpdate("INSERT INTO Bestellt (BestelltTeilnr, BestelltLieferantenID)" +
						" VALUES ("+ teilNr + "," + lieferantenID + ")");			
				
				conn.commit();
			
			System.out.println("Added "+ basisTeilName + " in size: " + size);
			
		} catch (NumberFormatException | IOException e) {
			System.out.println("IO Exception while creating a new machine");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("SQLException while creating a new machine");
			e.printStackTrace();
		}
	}

	//Dialog to add a Lieferant to the database
	public int addLieferant(Connection conn) {

		String name = null;
		String address = null;
		String bank = null;
		int lieferantenID = 0;
		
		try {
			System.out.println("Please enter the name of the Hersteller: ");
			name = bin.readLine();

			System.out.println("Please enter the name of the address of the Hersteller: ");
			address = bin.readLine();
			
			System.out.println("Please enter the bank of the address of the Hersteller: ");
			bank = bin.readLine();

		Statement myQuery;
			myQuery = conn.createStatement();

			myQuery.executeUpdate("INSERT INTO Lieferant (Name, Adresse, Bankverbindung)" +
					" VALUES ('"+name+"', '"+address+"', '"+bank+"')");	

			conn.commit();
			
			Statement stmt = conn.createStatement ();
			ResultSet rs = stmt.executeQuery("SELECT *" +
					" FROM Lieferant" +
					" WHERE LieferantenID=(SELECT MAX(LieferantenID) FROM Lieferant)");		
			rs.next();
			lieferantenID = rs.getInt("LieferantenID");
			rs.close();			

		} catch (SQLException e) {
			System.out.println("SQLException while trying to add a Lieferant");
			e.printStackTrace();
		} catch (IOException e) {
			System.out.println("IOException while trying to add a Lieferant");
			e.printStackTrace();
		}
		return lieferantenID;
	}
	
	// Starts the connection to the database Fertigungsplanung FOR DEBUGGING on JOEL PC!!!!!
	public Connection startConnection() {
		Connection conn = null;

		try {
			Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

			conn = DriverManager
					.getConnection(
							"jdbc:sqlserver://localhost\\dmg2012;Database=dmg_project",
							"sa", "DMG2012GR09");
			conn.setAutoCommit(false);

		} catch (ClassNotFoundException e) {
			System.out.println("Could not find class");
			e.printStackTrace();
		} catch (SQLException e) {
			System.out.println("Could not connect to the database");
			e.printStackTrace();
		}
		return conn;
	}
	
	
}

